﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Office = Microsoft.Office.Interop;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text.RegularExpressions;
using System.IO;
using System.Collections;
using System.Globalization;
using System.Diagnostics;
using System.ComponentModel;
using System.Collections.ObjectModel;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;




namespace Vaffanculo.Presentation
{
    class ClassExcel
    {

        internal delegate void onChange_Progress(int Maximum, int Current, string Message);
        internal event onChange_Progress _onChange_Progress;


        private Excel.Application m_objExcel;
        private Excel.Workbooks m_objBooks;
        private Excel.Workbook m_objBook;
        private Excel.Sheets m_objSheets;


        private Color ColorTan_Light = Color.FromArgb(221, 217, 195);
        private Color ColorTan_Medium = Color.FromArgb(197, 190, 151);
        private Color ColorBlue_Light = Color.FromArgb(197, 217, 241);


        /// <summary>
        /// Struct that defines the data row of the excel sheet
        /// </summary>
        private struct dummyDataRow
        {
            internal int ID;
            internal string projectName;
            internal string targetLanguageName;
            internal int fileWordsTotal;
            internal int fileWordsContextMatch;
            internal int fileWords100p;
            internal int fileWords99_95p;
            internal int fileWords94_85p;
            internal int fileWords84_75p;
            internal int fileWords74_50p;
            internal int fileWords50_0p;
        }

        #region  |  Private Procedures  |

        private List<dummyDataRow> getDummyDataRows()
        {
            #region  |  Setup some dummy data  |

            List<dummyDataRow> dummyDataRows = new List<dummyDataRow>();

            dummyDataRow _dummyDataRow = new dummyDataRow();
            _dummyDataRow.ID = 1;
            _dummyDataRow.projectName = "Proj_1";
            _dummyDataRow.targetLanguageName = "Bolivian";
            _dummyDataRow.fileWordsTotal = 150;
            _dummyDataRow.fileWordsContextMatch = 0;
            _dummyDataRow.fileWords100p = 25;
            _dummyDataRow.fileWords99_95p = 5;
            _dummyDataRow.fileWords94_85p = 12;
            _dummyDataRow.fileWords84_75p = 10;
            _dummyDataRow.fileWords74_50p = 8;
            _dummyDataRow.fileWords50_0p = 90;
            dummyDataRows.Add(_dummyDataRow);


            _dummyDataRow = new dummyDataRow();
            _dummyDataRow.ID = 2;
            _dummyDataRow.projectName = "Proj_2";
            _dummyDataRow.targetLanguageName = "Bolivian";
            _dummyDataRow.fileWordsTotal = 176;
            _dummyDataRow.fileWordsContextMatch = 53;
            _dummyDataRow.fileWords100p = 80;
            _dummyDataRow.fileWords99_95p = 0;
            _dummyDataRow.fileWords94_85p = 12;
            _dummyDataRow.fileWords84_75p = 10;
            _dummyDataRow.fileWords74_50p = 18;
            _dummyDataRow.fileWords50_0p = 3;
            dummyDataRows.Add(_dummyDataRow);


            _dummyDataRow = new dummyDataRow();
            _dummyDataRow.ID = 3;
            _dummyDataRow.projectName = "Proj_3";
            _dummyDataRow.targetLanguageName = "Bolivian";
            _dummyDataRow.fileWordsTotal = 15;
            _dummyDataRow.fileWordsContextMatch = 3;
            _dummyDataRow.fileWords100p = 3;
            _dummyDataRow.fileWords99_95p = 2;
            _dummyDataRow.fileWords94_85p = 0;
            _dummyDataRow.fileWords84_75p = 0;
            _dummyDataRow.fileWords74_50p = 6;
            _dummyDataRow.fileWords50_0p = 1;
            dummyDataRows.Add(_dummyDataRow);

            _dummyDataRow = new dummyDataRow();
            _dummyDataRow.ID = 4;
            _dummyDataRow.projectName = "Proj_4";
            _dummyDataRow.targetLanguageName = "Bolivian";
            _dummyDataRow.fileWordsTotal = 159;
            _dummyDataRow.fileWordsContextMatch = 6;
            _dummyDataRow.fileWords100p = 3;
            _dummyDataRow.fileWords99_95p = 25;
            _dummyDataRow.fileWords94_85p = 45;
            _dummyDataRow.fileWords84_75p = 10;
            _dummyDataRow.fileWords74_50p = 8;
            _dummyDataRow.fileWords50_0p = 62;
            dummyDataRows.Add(_dummyDataRow);

            #endregion

            return dummyDataRows;
        }

        private void addDataToExcelSheet(Excel.Worksheet _m_objSheet_Data, List<dummyDataRow> _dummyDataRows)
        {
            //setup a row index counter for the data sheet
            int CurrentRowIndex_Data = 1;


            #region  |  Insert Data Header Row Example  |


            if (_onChange_Progress != null)
                _onChange_Progress(_dummyDataRows.Count, CurrentRowIndex_Data, "Inserting data headers");

            Excel.Range m_objRange_header;



            m_objRange_header = _m_objSheet_Data.get_Range("A" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Project Name";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);


            m_objRange_header = _m_objSheet_Data.get_Range("B" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Target Language";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);


            m_objRange_header = _m_objSheet_Data.get_Range("C" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Total Words";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);


            m_objRange_header = _m_objSheet_Data.get_Range("D" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Context Matches";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);

            m_objRange_header = _m_objSheet_Data.get_Range("E" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Words (100)";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);

            m_objRange_header = _m_objSheet_Data.get_Range("F" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Words (99-95)";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);

            m_objRange_header = _m_objSheet_Data.get_Range("G" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Words (94-85)";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);


            m_objRange_header = _m_objSheet_Data.get_Range("H" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Words (84-75)";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);

            m_objRange_header = _m_objSheet_Data.get_Range("I" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Words (74-50)";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);

            m_objRange_header = _m_objSheet_Data.get_Range("J" + CurrentRowIndex_Data, Missing.Value);
            m_objRange_header.Value2 = "Words (50-0)";
            m_objRange_header.WrapText = false;
            m_objRange_header.ColumnWidth = 12;
            m_objRange_header.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            m_objRange_header.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            m_objRange_header.Font.Bold = true;
            m_objRange_header.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            m_objRange_header.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTan_Medium);




            #endregion

            #region  |  Insert Data Row Example  |

            Excel.Range m_objRange_data;


            foreach (dummyDataRow _DataRow in _dummyDataRows)
            {

                CurrentRowIndex_Data++;

                if (_onChange_Progress != null)
                    _onChange_Progress(_dummyDataRows.Count, CurrentRowIndex_Data, "Inserting data rows");

                m_objRange_data = _m_objSheet_Data.get_Range("A" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.projectName;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"@";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("B" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.targetLanguageName;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"@";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("C" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWordsTotal;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;


                m_objRange_data = _m_objSheet_Data.get_Range("D" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWordsContextMatch;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("E" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWords100p;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("F" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWords99_95p;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("G" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWords94_85p;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("H" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWords84_75p;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("I" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWords74_50p;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;

                m_objRange_data = _m_objSheet_Data.get_Range("J" + CurrentRowIndex_Data, Missing.Value);
                m_objRange_data.Value2 = _DataRow.fileWords50_0p;
                m_objRange_data.WrapText = false;
                m_objRange_data.NumberFormat = @"#,##0";
                m_objRange_data.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                m_objRange_data.Interior.ColorIndex = 0;


            }



            #endregion

            #region  |  Perform some formating on the datarange  |

            //Exmaple of formatting the datarage after you have finished inserting the data

            //Note: it is quicker to perform the majority of the formatting after
            //you have finished inserting the data.

            Excel.Range m_objRange = _m_objSheet_Data.get_Range("A2", "J" + CurrentRowIndex_Data);
            m_objRange.Font.Name = m_objExcel.StandardFont;
            m_objRange.Font.Size = m_objExcel.StandardFontSize;
            m_objRange.Font.Bold = false;
            m_objRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
            Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);

            #endregion
        }

        private void addPivotTable(Excel.Worksheet _m_objSheet_Data, Excel.Worksheet _m_objSheet_Report_1)
        {
            #region  |  Pivot Table  |


            if (_onChange_Progress != null)
                _onChange_Progress(1, 1, "Adding pivot table...");


            //get a reference to the pivot chache
            Excel.PivotCaches pch = m_objBook.PivotCaches();

            //Activate the data sheet before adding the range to the pivot table
            _m_objSheet_Data.Activate();

            //add a pivot table reference to the sheet            
            pch.Add(Excel.XlPivotTableSourceType.xlDatabase, "'" + _m_objSheet_Data.Name + "'!A1:'" + _m_objSheet_Data.Name + "'!J5").CreatePivotTable(_m_objSheet_Report_1.Cells[1, 1], "PivTbl_1", Type.Missing, Type.Missing);
            Excel.PivotTable pvt = _m_objSheet_Report_1.PivotTables("PivTbl_1") as Excel.PivotTable;




            //Data
            Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("Context Matches"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
            fld.NumberFormat = "#,##0_);[Red](#,##0)";

            //Data
            fld = ((Excel.PivotField)pvt.PivotFields("Words (100)"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
            fld.NumberFormat = "#,##0_);[Red](#,##0)";

            //Data
            fld = ((Excel.PivotField)pvt.PivotFields("Words (99-95)"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
            fld.NumberFormat = "#,##0_);[Red](#,##0)";

            //Data
            fld = ((Excel.PivotField)pvt.PivotFields("Words (94-85)"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
            fld.NumberFormat = "#,##0_);[Red](#,##0)";

            //Data
            fld = ((Excel.PivotField)pvt.PivotFields("Words (84-75)"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
            fld.NumberFormat = "#,##0_);[Red](#,##0)";

            //Data
            fld = ((Excel.PivotField)pvt.PivotFields("Words (74-50)"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
            fld.NumberFormat = "#,##0_);[Red](#,##0)";

            //Data
            fld = ((Excel.PivotField)pvt.PivotFields("Words (50-0)"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
            fld.NumberFormat = "#,##0_);[Red](#,##0)";


            //Filter (Single Item Filter)
            Excel.PivotField fld_targetLanguage = ((Excel.PivotField)pvt.PivotFields("Target Language"));
            fld_targetLanguage.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            fld_targetLanguage.CurrentPage = "Italian";



            //Filter (Multiple Item Filter)
            Excel.PivotField fld_projectName = ((Excel.PivotField)pvt.PivotFields("Project Name"));
            fld_projectName.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

            //fld_projectName.CubeField.EnableMultiplePageItems

            //dummy filter list that we want to apply on the data
            List<string> ListToFilter = new List<string>();
            ListToFilter.Add("Proj_1");
            ListToFilter.Add("Proj_2");



            //initialize the filter ensuring that all items are checked/selected
            fld_projectName.CurrentPage = "(All)";


            //Apply the 'Multiple Items' filter on the pivot table.
            //
            //This should resemble the same functionality as manullay 
            //checking the checkbox for 'Select Multiple Items'
            //and then selecting multiple items for the filter.
            Excel.PivotFields _PivotFields = (Excel.PivotFields)pvt.get_PageFields(Missing.Value);

            foreach (Excel.PivotField _PivotField in _PivotFields)
            {
                if (string.Compare(_PivotField.Caption, fld_projectName.Caption, true) == 0)
                {
                    Excel.PivotItems _PivotItems = (Excel.PivotItems)_PivotField.PivotItems(Missing.Value);

                    foreach (Excel.PivotItem _PivotItem in _PivotItems)
                    {
                        //Note: only the filter items whose items match ("Proj_1, Proj_2") 
                        //will be checked/selected
                        if (ListToFilter.Contains(_PivotItem.Caption))
                            _PivotItem.Visible = true;
                        else
                            _PivotItem.Visible = false;
                    }
                }
            }



            //define how the xlDataField values are aligned
            pvt.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;




            #endregion
        }

        private void addPivotChart(Excel.Worksheet _m_objSheet_Report_1)
        {
            #region  |  Chart Object  |

            if (_onChange_Progress != null)
                _onChange_Progress(1, 1, "Adding pivot chart...");

            Excel.Range _m_objRange_Chart;

            //Ensure that the Pivot Table has focus; then the chart object will automatically inherit the reference.
            _m_objRange_Chart = _m_objSheet_Report_1.get_Range("A1", "A1");
            //_m_objRange_Chart.Activate();


            //Add the new chart object and have fun :-)
            Excel.ChartObjects _ChartObjects = (Excel.ChartObjects)(_m_objSheet_Report_1.ChartObjects(Missing.Value));
            Excel.ChartObject _ChartObject = _ChartObjects.Add(170, 0, 400, 300);
            Excel.Chart _Chart = _ChartObject.Chart;


            _Chart.SetSourceData(_m_objRange_Chart, Excel.XlRowCol.xlColumns);

            _Chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPieExploded;
            _Chart.HasTitle = true;
            _Chart.ChartTitle.Text = "Italian Word Count % - Example";
            _Chart.ChartTitle.Font.Size = 12;

            _Chart.ApplyDataLabels(
                Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowNone
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value, false
                , Missing.Value, true, Missing.Value, Missing.Value);


            _Chart.Rotation = 0; //X
            _Chart.Elevation = 60; //Y
            _Chart.Perspective = 10; //Perspective
            _Chart.DepthPercent = 100; //Depth

            _ChartObject.Chart.HasLegend = true;

            _m_objSheet_Report_1.Activate();

            #endregion
        }

        #endregion

        #region  |  Internal procedures  |

        internal void createNewXLSFile(bool isVisible)
        {
            //get a reference to the interop
            m_objExcel = new Excel.Application();

            //set some default values
            m_objExcel.Visible = isVisible;
            m_objExcel.StandardFont = "Arial";
            m_objExcel.StandardFontSize = 10;

            //get a reference to the Excel workbooks
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

            //add a new workbook
            m_objBooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, m_objBooks, null, Application.CurrentCulture);
            m_objBook = (Excel.Workbook)(m_objBooks.get_Item(1));

            //get reference to the worksheets
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

            //Remove all but 1 sheet
            //Note: the work book adds 3 sheets by default; this simply removes the surplus sheets
            for (int i = (m_objSheets.Count - 1); i > 0; i--)
            {
                Excel.Worksheet m_objSheet = (Excel.Worksheet)(m_objSheets.get_Item(i));
                m_objSheet.Delete();
            }
        }

        internal void createWorksheetExample()
        {



            //Create a new worksheet to hold the data
            Excel.Worksheet m_objSheet_Data = (Excel.Worksheet)(m_objSheets.get_Item((1)));
            m_objSheet_Data.Name = "Data";
            m_objExcel.ActiveWindow.DisplayGridlines = false;

            //Create a dummydatarow list to populate the excel sheet
            List<dummyDataRow> dummyDataRows = getDummyDataRows();

            //Add the data rows to the excel sheet
            addDataToExcelSheet(m_objSheet_Data, dummyDataRows);



            //Create a new worksheet to hold the pivot tabel and chart
            Excel.Worksheet m_objSheet_Report_1 = (Excel.Worksheet)(m_objSheets.Add(Missing.Value, m_objSheets.get_Item(m_objSheets.Count), Missing.Value, Missing.Value));
            m_objSheet_Report_1.Name = "Report Example 1";



            addPivotTable(m_objSheet_Data, m_objSheet_Report_1);

            addPivotChart(m_objSheet_Report_1);

        }

        internal void saveWorkBook(FileInfo fi)
        {


            Application.DoEvents();

            m_objExcel.DisplayAlerts = false;

            if (fi.Exists)
                fi.Delete();

            m_objBook.SaveAs(fi.FullName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            if (!m_objExcel.Visible)
                m_objBook.Close(false, Missing.Value, Missing.Value);
        }

        internal void closeApplication()
        {
            if (m_objExcel != null && !m_objExcel.Visible)
            {
                if (m_objExcel != null)
                {
                    m_objExcel.Quit();
                    m_objExcel = null;
                }

                Process[] Processes;

                Processes = Process.GetProcessesByName("EXCEL");
                foreach (Process p in Processes)
                {
                    if (p.MainWindowTitle.Trim() == "")
                        p.Kill();
                }
            }
        }

        #endregion


    }
}

